package dao.common;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;

import model.common.Goods;
import model.common.StockPlan;


public class StockPlanDao {
   
	public void addStockPlan(StockPlan sp) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_stockplan " + "(goodsName,goodsUnitPrice,"
				    + " goodsPrice,goodsSum,paymentDate,purcharseDate,deliveryDate,goodsProductionDate) "
				    + " values(?,?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, sp.getGoodsName());
		ptmt.setDouble(2, sp.getGoodsUnitPrice());
		ptmt.setDouble(3, sp.getGoodsPrice());
		ptmt.setDouble(4, sp.getGoodsSum());
		ptmt.setDate(5, new java.sql.Date(sp.getPaymentDate().getTime()));
		ptmt.setDate(6, new java.sql.Date(sp.getPurcharseDate().getTime()));
		ptmt.setDate(7, new java.sql.Date(sp.getDeliveryDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(sp.getGoodsProductionDate().getTime()));
		
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}
	
	public void deleteStockPlan(Integer goodsStockId) throws SQLException {
		Connection conn = DBHelper.getConnection();   
		String sql = " delete from tab_stockplan  where goodsStockId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsStockId);
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}

	public void updateStockPlan(StockPlan sp) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_stockplan set goodsName=?,goodsUnitPrice=?,goodsPrice=?,"
		+ " goodsSum=?,paymentDate=?,purcharseDate=?,deliveryDate=?,goodsProductionDate=?"
				+ " where goodsStockId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, sp.getGoodsName());
		ptmt.setDouble(2, sp.getGoodsUnitPrice());
		ptmt.setDouble(3, sp.getGoodsPrice());
		ptmt.setDouble(4, sp.getGoodsSum());
		ptmt.setDate(5, new java.sql.Date(sp.getPaymentDate().getTime()));
		ptmt.setDate(6, new java.sql.Date(sp.getPurcharseDate().getTime()));
		ptmt.setDate(7, new java.sql.Date(sp.getDeliveryDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(sp.getGoodsProductionDate().getTime()));
		ptmt.setInt(9, sp.getGoodsStockId());
		
		ptmt.execute();
	/*	ptmt.close();
		conn.close();*/
	}
	
	public StockPlan get(Integer goodsStockId) throws SQLException {
		StockPlan sp = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_stockplan where goodsStockId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsStockId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			sp = new StockPlan();
			sp.setGoodsStockId(rs.getInt("goodsStockId"));
			sp.setGoodsName(rs.getString("goodsName"));
			sp.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			sp.setGoodsPrice(rs.getDouble("goodsPrice"));
			sp.setGoodsSum(rs.getDouble("goodsSum"));
			sp.setPaymentDate(rs.getDate("paymentDate"));
			sp.setPurcharseDate(rs.getDate("purcharseDate"));
			sp.setDeliveryDate(rs.getDate("deliveryDate"));
			sp.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
		}
		
		rs.close();
		ptmt.close();
		conn.close();
		return sp;
		
	}
	
	public List<StockPlan> query(List<Map<String, Object>> params) throws SQLException{
		List<StockPlan> result=new ArrayList<StockPlan>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_stockplan where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		StockPlan sp = null;
		while (rs.next()) {
		sp = new StockPlan();
		sp.setGoodsStockId(rs.getInt("goodsStockId"));
		sp.setGoodsName(rs.getString("goodsName"));
		sp.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
		sp.setGoodsPrice(rs.getDouble("goodsPrice"));
		sp.setGoodsSum(rs.getDouble("goodsSum"));
		sp.setPaymentDate(rs.getDate("paymentDate"));
		sp.setPurcharseDate(rs.getDate("purcharseDate"));
		sp.setDeliveryDate(rs.getDate("deliveryDate"));
		sp.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			result.add(sp);
		}
		/*rs.close();
		ptmt.close();
		conn.close();*/
		return result;
		}
	
	public List<StockPlan> query() throws Exception{
		List<StockPlan> result=new ArrayList<StockPlan>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select goodsStockId,goodsName,goodsSum from tab_stockplan ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		StockPlan sp=null;
		while(rs.next()){
			sp=new StockPlan();
			sp.setGoodsStockId(rs.getInt("goodsStockId"));
			sp.setGoodsName(rs.getString("goodsName"));
			sp.setGoodsSum(rs.getDouble("goodsSum"));
			result.add(sp);
		}
		rs.close();
		ptmt.close();
		conn.close();
		return result;
	}
	
	public List<StockPlan> query(String goodsName,String goodsPrice) throws Exception{
		List<StockPlan> result=new ArrayList<StockPlan>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_stockplan  ");
		
		sb.append(" where goodsName like ? and goodsPrice like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+goodsName+"%");
		ptmt.setString(2, "%"+goodsPrice+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		StockPlan sp=null;
		while(rs.next()){
			sp=new StockPlan();
			sp.setGoodsStockId(rs.getInt("goodsStockId"));
			sp.setGoodsName(rs.getString("goodsName"));
			sp.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			sp.setGoodsPrice(rs.getDouble("goodsPrice"));
			sp.setGoodsSum(rs.getDouble("goodsSum"));
			sp.setPaymentDate(rs.getDate("paymentDate"));
			sp.setPurcharseDate(rs.getDate("purcharseDate"));
			sp.setDeliveryDate(rs.getDate("deliveryDate"));
			sp.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			result.add(sp);
		}
		rs.close();
		ptmt.close();
		conn.close();
		return result;
		
	}
	


	
}
